#!/usr/local/bin/php
<?php
	error_reporting(E_ALL);
	ini_set('display_errors', 1);
		require_once('sync_config.php');
	error_log(date('d/m/Y H:i:s') . " | Start cron job \n", 3, $log_file);

	// connect to source database
	$src_db_con = mysql_connect($src_db_host, $src_db_username, $src_db_password);
	if(!$src_db_con || !mysql_select_db($src_db_name, $src_db_con)){
		error_log(date('d/m/Y H:i:s') . " | Could not connect to source database, or the database name is incorrect \n", 3, $log_file);
		$fp = fopen($fail_datetime_file, 'a+');
		fputcsv($fp, array(date('m/d/Y H:i:s')));
		fclose($fp);
		error_log(date('d/m/Y H:i:s') . " | End of cron job \n", 3, $log_file);
		exit;
	}

	// connect to destination database
	$dst_db_con = mysql_connect($dst_db_host, $dst_db_username, $dst_db_password);
	if(!$dst_db_con || !mysql_select_db($dst_db_name, $dst_db_con)){
		error_log(date('d/m/Y H:i:s') . " | Can not connect to destination database, or the database name is incorrect \n", 3, $log_file);
		$fp = fopen($fail_datetime_file, 'a+');
		fputcsv($fp, array(date('m/d/Y H:i:s')));
		fclose($fp);
		error_log(date('d/m/Y H:i:s') . " | End of cron job \n", 3, $log_file);
		exit;
	}

	// synchronize for the previous hour
	error_log(date('d/m/Y H:i:s') . " | Synchronize data for the previous hour \n", 3, $log_file);
	if(sync(date('m/d/Y H:i:s'), $src_db_con, $dst_db_con, $fail_datetime_file, $log_file)){
		error_log(date('d/m/Y H:i:s') . " | Synchronize data for the previous failed hours \n", 3, $log_file);
		$fp = fopen($fail_datetime_file, "a+");
		$fail_datetime_array = fgetcsv($fp, null, ',');
		fclose($fp);
		while($fail_datetime_array){
			$fail_datetime = array_pop($fail_datetime_array);
			if(!sync($fail_datetime, $src_db_con, $dst_db_con, $fail_datetime_file, $log_file)){
				$fp = fopen($fail_datetime_file, 'w+');
				fputcsv($fp, $fail_datetime_array, ',' );
				fclose($fp);
				error_log(date('d/m/Y H:i:s') . " | End of cron job \n", 3, $log_file);
				break;
			}
		}
		if(count($fail_datetime_array) == 0){
			$fp = fopen($fail_datetime_file, 'w+');
			fclose($fp);
		}
	}
	error_log(date('d/m/Y H:i:s') . " | Exit cron job \n", 3, $log_file);

	// close the databases
	mysql_close($src_db_con);
	mysql_close($dst_db_con);

	function sync($datetime, $src_db_con, $dst_db_con, $fail_datetime_file, $log_file){
		global $fee;
		$timestamp = strtotime($datetime);
		// get the select date time
		$prevTimestamp = $timestamp - 60*60;
		$currDay = date('d', $timestamp);
		$currHour = date('H', $timestamp);
		$currMonth = date('m', $timestamp);
		$currYear = date('Y', $timestamp);

		$prevDay = date('d', $prevTimestamp );
		$prevMonth = date('m', $prevTimestamp );
		$prevHour = date('H', $prevTimestamp);
		$prevYear = date('Y', $prevTimestamp);
		$prevDatetime = $prevMonth . "/" . $prevDay . "/" . $prevYear . " " . $prevHour . ":00:00";
		$currDatetime = $currMonth . "/" . $currDay . "/" . $currYear . " " . $currHour . ":00:00";

		error_log(date('d/m/Y H:i:s') . " | Synchronize for date: $prevDatetime \n", 3, $log_file);

		$mo_table = 'mo' . $prevYear . $prevMonth . $prevDay;
		$mt_table = 'mt' . $prevYear . $prevMonth . $prevDay;
		$cdr_table = 'cdrreport' . $prevYear . $prevMonth . $prevDay;
		$billing_table = 'vmg_billing_count' . $prevYear . $prevMonth;
		$dest_table = 'vmg_billing_count' . $prevYear . $prevMonth;

		// Create table if not exist
		$create_table = "CREATE TABLE IF NOT EXISTS `" . $dest_table . "` (
			  `id` int(11) NOT NULL auto_increment,
			  `date` datetime NOT NULL,
			  `msisdn` varchar(20) NOT NULL,
			  `operator` varchar(64) NOT NULL,
			  `short_code` varchar(20) NOT NULL,
			  `mo_count` int(11) NOT NULL,
			  `mt_count` int(11) NOT NULL,
			  `cdr_count` int(11) NOT NULL default '0',
			  `error_mo` int(11) NOT NULL default '0',
			  `error_mt` int(11) NOT NULL default '0',
			  `money` int(11) NOT NULL default '0',
			  `repeated_cdr` int(11) NOT NULL default '0',
			  `spam` int(11) NOT NULL default '0',
			  PRIMARY KEY  (`id`)
		) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=1;";

		if(!mysql_query($create_table, $dst_db_con)){
			error_log(date('d/m/Y H:i:s') . " | Can not create the source database table \n", 3, $log_file);
			error_log(date('d/m/Y H:i:s') . " | End of synchronization for date: $prevDatetime \n", 3, $log_file);
			$fp = fopen($fail_datetime_file, 'a+');
			fputcsv($fp, array(date('m/d/Y H:i:s')));
			fclose($fp);
			return false;
		}else{
			error_log(date('d/m/Y H:i:s') . " | Destination database table created \n", 3, $log_file);
		}

		// select then update data
		$select =
		"select a.*,b.cdrcount
			from
			(select mo.*,mt.mtcount
			from
			(select str_to_date(date_format(date,'%m/%d/%Y %H:00:00'), '%m/%d/%Y %T')as dt, sender as msisdn, operator, phone as short_code, count(*) as mocount
			from $mo_table
			where date >= str_to_date('$prevDatetime', '%m/%d/%Y %H:00:00')
			and date < str_to_date('$currDatetime', '%m/%d/%Y %H:00:00')
			and service like 'VnnLinks:XZone%'
			and operator <> 'NA' and operator is not null and trim(operator) <> ''
			group by dt, msisdn, operator, short_code) mo,

			(select str_to_date(date_format(date,'%m/%d/%Y %H:00:00'), '%m/%d/%Y %T')as dt, phone as msisdn, operator, sender as short_code, count(*) as mtcount
			from $mt_table
			where date >= str_to_date('$prevDatetime', '%m/%d/%Y %H:00:00')
			and date < str_to_date('$currDatetime', '%m/%d/%Y %H:00:00')
			and service like 'VnnLinks:XZone%'
			and operator <> 'NA' and operator is not null and trim(operator) <> ''
			group by dt, msisdn, operator, short_code) mt

			where mo.msisdn = mt.msisdn and mo.short_code = mt.short_code and mo.dt = mt.dt) a,

			(select str_to_date(concat(substring(sendtime,1,10),':00:00'), '%Y%m%d%T')as dt, concat('+',userid) as msisdn, operator, serviceid as short_code, count(*) as cdrcount
			from $cdr_table
			where str_to_date(sendtime,'%Y%m%d%H%i%s') >= str_to_date('$prevDatetime', '%m/%d/%Y %H:00:00')
			and str_to_date(sendtime,'%Y%m%d%H%i%s') < str_to_date('$currDatetime', '%m/%d/%Y %H:00:00')
			and keyword like 'xz_%'
			and operator <> 'NA' and operator is not null and trim(operator) <> ''
			and typemessage = 1
			group by dt, msisdn, operator, short_code) b
			where a.msisdn = b.msisdn and a.short_code = b.short_code and a.dt = b.dt;
		";
		//error_log(date('d/m/Y H:i:s') . " | Query to run | $select \n", 3, $log_file);
		$result = mysql_query($select, $src_db_con);
		if(!$result){
			error_log(date('d/m/Y H:i:s') . " | Could not successfully run query from the source database | $select | "
				. mysql_error($src_db_con) . "\n", 3, $log_file);
			error_log(date('d/m/Y H:i:s') . " | End of synchronization for date: $prevDatetime \n", 3, $log_file);
			$fp = fopen($fail_datetime_file, 'a+');
			fputcsv($fp, date('m/d/Y H:i:s') );
			fclose($fp);
			return false;
		}

		if (mysql_num_rows($result) == 0) {
		    error_log(date('d/m/Y H:i:s') . " | There is no data from the source database \n", 3, $log_file);
		    error_log(date('d/m/Y H:i:s') . " | End of synchronization for date: $prevDatetime \n", 3, $log_file);
			return false;
		}

		while ($row = mysql_fetch_assoc($result)) {
			$insert = 'INSERT INTO ' . $billing_table . "(date, msisdn, operator, short_code, mo_count, mt_count, cdr_count, money) VALUE";
			$operator = $row['operator'];
			$pos = strpos($operator, "_");
			if ($pos > 0) {
				$operator = substr($operator, 0, $pos - 1);
			}
			$operator = strtoupper($operator);
			$insert .= "('" . $row["dt"] . "','" . $row['msisdn'] . "','" . $operator . "','" . $row["short_code"] . "'," . $row["mocount"] . ","
					. $row["mtcount"] . "," . $row["cdrcount"] . "," . ($fee[$operator][$row["short_code"]] * $row["cdrcount"]) . ")";
			$rt = mysql_query($insert, $dst_db_con);
			if(!$rt){
				error_log(date('d/m/Y H:i:s') . " | Could not insert the data into the destination database | " . $insert . " | " . mysql_error($dst_db_con) . "\n", 3, $log_file);
			}else{
				error_log(date('d/m/Y H:i:s') . " | Inserted the data into the destination database |" . mysql_error($dst_db_con) . "\n", 3, $log_file);
			}
		}
		mysql_free_result($result);
		error_log(date('d/m/Y H:i:s') . " | End of synchronization for date: $prevDatetime \n", 3, $log_file);
		return true;
	}

?>
